﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public  class DTaxiRouteHistory
    {
       
        public bool Exists(string phoneNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tTaxiRouteHistory");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.String)
             };
            parameters[0].Value = phoneNumber;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }

     
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.TaxiRouteHistoryInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tTaxiRouteHistory(");
            strSql.Append("PhoneNumber,StartTime,EndTime,");
            strSql.Append("Distance,State,ServerTime)");
            strSql.Append(" values (");
            strSql.Append("@PhoneNumber,@StartTime,@EndTime,");
            strSql.Append("@Distance,@State,@ServerTime)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15),
					new MySqlParameter("@StartTime", MySqlDbType.DateTime),
                    new MySqlParameter("@EndTime", MySqlDbType.DateTime),                  
                    new MySqlParameter("@Distance", MySqlDbType.Double),
                    new MySqlParameter("@State", MySqlDbType.Int32,10),
                    new MySqlParameter("@ServerTime", MySqlDbType.DateTime)};
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.StartTime;
            parameters[2].Value = model.EndTime;      
            parameters[3].Value = model.Distance;
            parameters[4].Value = model.State;
            parameters[5].Value = model.ServerTime;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.TaxiRouteHistoryInfo model)
        {

            //,StartLat,StartLng,");
            //strSql.Append("EndLat,EndLng,Distance,State)");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tTaxiRouteHistory set ");
            strSql.Append("PhoneNumber=@PhoneNumber,");
            strSql.Append("StartTime=@StartTime,");
            strSql.Append("EndTime=@EndTime,");       
            strSql.Append("Distance=@Distance,");
            strSql.Append("State=@State");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15),
					new MySqlParameter("@StartTime", MySqlDbType.DateTime),
                    new MySqlParameter("@EndTime", MySqlDbType.DateTime),                
                    new MySqlParameter("@Distance", MySqlDbType.Double),
                    new MySqlParameter("@State", MySqlDbType.Int32,10),
                    new MySqlParameter("@id", MySqlDbType.Int32,10)};
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.StartTime;
            parameters[2].Value = model.EndTime;    
            parameters[3].Value = model.Distance;
            parameters[4].Value = model.State;
            parameters[5].Value = model.id;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tTaxiRouteHistory ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tTaxiRouteHistory ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM tTaxiRouteHistory ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得车辆指定时间内数据列表
        /// </summary>
        public DataSet GetListByPhoneNumberAndTime(string phoneNumber,string startTime,string endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.PhoneNumber,t.StartTime,");
            strSql.Append("t.EndTime,t.Distance,t.ServerTime,");
            strSql.Append("(case t.State when '1' then '空载' ");
            strSql.Append(" when '0' then '载客' end) as State,");
            strSql.Append("tdriver.Name,ttaxi.TaxiNo");
            strSql.Append(",tcompany.CompanyName ");
            strSql.Append(" FROM tTaxiRouteHistory t ");
            strSql.Append("left join tdriver on t.PhoneNumber = tdriver.PhoneNumber ");
            strSql.Append("left join ttaxi on tdriver.taxiid = ttaxi.id ");
            strSql.Append("left join tcompany on ttaxi.CompanyId = tcompany.Id ");
            strSql.Append("where StartTime between '");
            strSql.Append(startTime);
            strSql.Append("' and '");
            strSql.Append(endTime);
            strSql.Append("' ");
            if (phoneNumber.Trim()!="")
            {
                strSql.Append(" and t.PhoneNumber ='");
                strSql.Append(phoneNumber);
                strSql.Append("'");
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        public DataSet GetStatisticsByPara(string phoneNumber,string startTime,string endTime)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select t.PhoneNumber,date(StartTime)  as 'Date', ");
            //strSql.Append("sum(case State when '1' then Distance end) as KongZai,");
            //strSql.Append("sum(case State when '0' then Distance end) as ZaiKe,"); 
            //strSql.Append("tdriver.Name,ttaxi.TaxiNo");

            //strSql.Append(",sum(Distance) as TotalDistance,");
            //strSql.Append("count(case State when '0' then t.PhoneNumber end)");
            //strSql.Append(" as OrderCount ");

            //strSql.Append(" FROM tTaxiRouteHistory t ");
            //strSql.Append("left join tdriver on t.PhoneNumber = tdriver.PhoneNumber ");
            //strSql.Append("left join ttaxi on tdriver.taxiid = ttaxi.id ");
            //strSql.Append("where  StartTime Between '");
            //strSql.Append(startTime);
            //strSql.Append("' and '");
            //strSql.Append(endTime);
            //strSql.Append("' ");    
            //if (phoneNumber.Trim()!="")
            //{
            //    strSql.Append("and t.PhoneNumber='");
            //    strSql.Append(phoneNumber);
            //    strSql.Append("'");
            //}
            //strSql.Append(" group By date(StartTime),t.PhoneNumber");
            //return DbHelperMySQL.Query(strSql.ToString());

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.PhoneNumber,StartTime,");
            strSql.Append("EndTime,Distance,State,");
            strSql.Append("tdriver.Name,ttaxi.TaxiNo");
            strSql.Append(",tcompany.CompanyName ");
            strSql.Append(" FROM tTaxiRouteHistory t ");
            strSql.Append("left join tdriver on t.PhoneNumber = tdriver.PhoneNumber ");
            strSql.Append("left join ttaxi on tdriver.taxiid = ttaxi.id ");
            strSql.Append("left join tcompany on ttaxi.CompanyId = tcompany.Id ");
            strSql.Append("where  StartTime Between '");
            strSql.Append(startTime);
            strSql.Append("' and '");
            strSql.Append(endTime);
            strSql.Append("' ");
            if (phoneNumber.Trim() != "")
            {
                strSql.Append("and t.PhoneNumber='");
                strSql.Append(phoneNumber);
                strSql.Append("'");
            }
            strSql.Append(" order By t.PhoneNumber");
            return DbHelperMySQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="intObject">0表示单辆车，1表示所有车</param>
        /// <param name="strValue">如果为单量车 该值为手机号</param>
        /// <param name="dateType">时间统计</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
          
        public DataSet GetStatisReportResult(int intObject, string strValue,
            int dateType,string startTime,string endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            //strSql.Append("sum(case State when '0' then Distance end)/");
            //strSql.Append("sum(Distance)*100 as KongZai,");
            //strSql.Append("sum(case State when '1' then Distance end)/");
            //strSql.Append("sum(Distance)*100 as ZaiKe");

            strSql.Append("cast(sum(case State when '1' then Distance end)");
            strSql.Append(" as decimal(9,2)) as KongZai,");
            strSql.Append("cast(sum(case State when '0' then Distance end)");
            strSql.Append(" as decimal(9,2))  as ZaiKe");
             
            switch (dateType)
            {                    
                case 0: //按天分组
                    //strSql.Append(",DATE_FORMAT(Updatetime, '%Y-%m-%d' ) AS DT ");
                    strSql.Append(",DATE_FORMAT(StartTime, '%d' ) AS DT ");
                    break;
                case 1:  //按月分组
                    //strSql.Append(",DATE_FORMAT(Updatetime, '%Y-%m' ) AS DT ");
                    strSql.Append(",DATE_FORMAT(StartTime, '%m月' ) AS DT ");
                    break;
                case 3: //按年分组
                    strSql.Append(",DATE_FORMAT(StartTime, '%Y年' ) AS DT ");
                    break;
            }

            strSql.Append(" from tTaxiRouteHistory t  ");

            strSql.Append("left join tdriver on t.PhoneNumber = tdriver.PhoneNumber ");
            strSql.Append("left join ttaxi on tdriver.taxiid = ttaxi.id ");
            strSql.Append("left join tcompany on ttaxi.CompanyId = tcompany.Id ");

            strSql.Append("where StartTime between '");
            strSql.Append(startTime);
            strSql.Append("' and '");
            strSql.Append(endTime);
            strSql.Append("' ");            
            if (intObject == 0)
            {
                strSql.Append("and PhoneNumber = '");
                strSql.Append(strValue);
                strSql.Append("' ");
            } 
            strSql.Append(" group by DT order by DT asc");
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            return ds;

        }
    
    }
}
